﻿/*  Copyright (C) 2013 AmaroK86 (marcidm 'at' hotmail 'dot' com)
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.

 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.

 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
using System;
using System.IO;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
using System.ComponentModel;
using System.Collections.Generic;
using AmaroK86.MassEffect3.PCC;
using AmaroK86.MassEffect3.DLC;
using AmaroK86.MassEffect3.UnrealObjects;

namespace AmaroK86.MassEffect3.Texplorer
{
    internal class DBHandler
    {
        SQLiteConnection sqlDBConn;

        public string dbFileName = "textureDB.sqlite";

        public DBHandler()
        {
            sqlDBConn = new SQLiteConnection("Data Source=" + dbFileName + ";Version=3;");
            sqlDBConn.Open();
        }

        public void Close()
        {
            sqlDBConn.Close();
        }

        public void showRebuildDB()
        {
           new WindowProgressForm(rebuildDB, null).ShowDialog();
        }

        public void rebuildDB(WindowProgressForm dbprog, object args)
        {
            SQLiteConnection sqlDBMem = new SQLiteConnection("Data Source=:memory:");
            SQLiteCommand command;
            sqlDBMem.Open();
            sqlDBConn.BackupDatabase(sqlDBMem, "main", "main", -1, null, 0);
            sqlDBConn.Close();

            string emptyDB = "DELETE FROM 'tbl_Pcc'";
            command = new SQLiteCommand(emptyDB, sqlDBMem);
            command.ExecuteNonQuery();
            emptyDB = "DELETE FROM 'tbl_Tex'";
            command = new SQLiteCommand(emptyDB, sqlDBMem);
            command.ExecuteNonQuery();
            emptyDB = "DELETE FROM 'tbl_TexPcc'";
            command = new SQLiteCommand(emptyDB, sqlDBMem);
            command.ExecuteNonQuery();
            emptyDB = "DELETE FROM sqlite_sequence";
            command = new SQLiteCommand(emptyDB, sqlDBMem);
            command.ExecuteNonQuery();

            List<string> fileList = Directory.GetFiles(ME3Paths.cookedPath, "*.pcc", SearchOption.AllDirectories).ToList();
            List<string> usefulFileList = new List<string>();

            dbprog.Invoke((Action)delegate
            {
                dbprog.Text = "Rebuilding texture database";
                dbprog.lblCommand.Text = "Building database for regular game";
                dbprog.progressBar.Maximum = fileList.Count;
                dbprog.progressBar.Value = 0;
            });

            int count = 0;
            int increment = 0;

            string texPccInsertion = "INSERT INTO 'tbl_TexPcc' ('TexId','PccId') VALUES";

            foreach (string inputPath in fileList)
            {
                if (dbprog.backgroundWorker.CancellationPending)
                {
                    sqlDBConn = new SQLiteConnection("Data Source=" + dbFileName + ";Version=3;");
                    sqlDBConn.Open();

                    return;
                }

                dbprog.Invoke((Action)delegate { dbprog.progressBar.Value = increment++; });

                PCCObject pccFile = new PCCObject(inputPath);

                var texList = pccFile.Exports.Where(export => export.ClassName == "Texture2D").ToList();
                if (texList.Count == 0)
                    continue;

                dbprog.Invoke((Action)delegate { dbprog.richTextBox.Text = increment + "\\" + fileList.Count + " - " + Path.GetFileName(inputPath); });

                string pccInsertion = "INSERT INTO 'tbl_Pcc' ('Name') VALUES ('" + Path.GetFileName(inputPath) + "')";
                command = new SQLiteCommand(pccInsertion, sqlDBMem);
                command.ExecuteNonQuery();

                string recoverPccId = "SELECT Id FROM 'tbl_Pcc' WHERE Name = '" + Path.GetFileName(inputPath) + "'";
                command = new SQLiteCommand(recoverPccId, sqlDBMem);
                SQLiteDataReader reader = command.ExecuteReader();
                string PccId = "";
                while (reader.Read())
                    PccId = reader[0].ToString();

                foreach (PCCObject.ExportEntry export in texList)
                {
                    string TexId = "";
                    string fullTexName = export.PackageFullName + "." + export.ObjectName;

                    string textureExist = "select count(Id) from tbl_Tex where FullName = '" + fullTexName + "'";
                    command = new SQLiteCommand(textureExist, sqlDBMem);

                    if (Convert.ToInt32(command.ExecuteScalar()) == 0)
                    {
                        string textureInsertion = "INSERT INTO 'tbl_Tex' ('FullName') VALUES ('" + fullTexName + "')";
                        command = new SQLiteCommand(textureInsertion, sqlDBMem);
                        command.ExecuteNonQuery();

                        string recoverTexId = "SELECT last_insert_rowid()";
                        command = new SQLiteCommand(recoverTexId, sqlDBMem);
                        reader = command.ExecuteReader();
                        while (reader.Read())
                            TexId = reader[0].ToString();
                    }
                    else
                    {
                        string recoverTexId = "SELECT Id FROM 'tbl_Tex' WHERE FullName = '" + fullTexName + "'";
                        command = new SQLiteCommand(recoverTexId, sqlDBMem);
                        reader = command.ExecuteReader();
                        while (reader.Read())
                            TexId = reader[0].ToString();
                    }

                    texPccInsertion += " (" + TexId + "," + PccId + "),";

                    if (count++ > 450)
                    {
                        texPccInsertion = texPccInsertion.Substring(0, texPccInsertion.Length - 1);
                        command = new SQLiteCommand(texPccInsertion, sqlDBMem);
                        command.ExecuteNonQuery();

                        texPccInsertion = "INSERT INTO 'tbl_TexPcc' ('TexId','PccId') VALUES";
                        count = 0;
                    }
                }
            }

            texPccInsertion = texPccInsertion.Substring(0, texPccInsertion.Length - 1);
            command = new SQLiteCommand(texPccInsertion, sqlDBMem);
            command.ExecuteNonQuery();
            
            /*List<string> DLCList = Directory.GetDirectories(ME3Paths.DLCPath, "DLC_*", SearchOption.AllDirectories).ToList();
            foreach (string dlcFolder in DLCList)
            {
                string dlcName = dlcFolder.Split('\\').Last();
                string sfarPath = dlcFolder + @"\CookedPCConsole\Default.sfar";
                DLCHandler dlcHandler = new DLCHandler(sfarPath);

                string dlcInsertion = "INSERT INTO 'tbl_Dlc' ('FolderName') VALUES ('" + dlcName + "')";
                command = new SQLiteCommand(dlcInsertion, sqlDBMem);
                command.ExecuteNonQuery();

                string recoverDlcId = "SELECT Id FROM 'tbl_Dlc' WHERE FolderName = '" + dlcName + "'";
                command = new SQLiteCommand(recoverDlcId, sqlDBMem);
                SQLiteDataReader reader = command.ExecuteReader();
                string DlcId = "";
                while (reader.Read())
                    DlcId = reader[0].ToString();

                List<string> pccList = dlcHandler.fileList.Where(entry => Path.GetExtension(entry.fileName) == ".pcc").Select(entry => entry.fileName).ToList();
                pccList.Sort();
                foreach (string pccName in pccList)
                {
                    string pccInsertion = "INSERT INTO 'tbl_Pcc' ('Name','Dlc') VALUES ('" + pccName + "', '" + DlcId + "')";
                    command = new SQLiteCommand(pccInsertion, sqlDBMem);
                    command.ExecuteNonQuery();


                }
            }*/

            sqlDBConn = new SQLiteConnection("Data Source=" + dbFileName + ";Version=3;");
            sqlDBConn.Open();

            // save memory db to file
            sqlDBMem.BackupDatabase(sqlDBConn, "main", "main", -1, null, 0);
            sqlDBMem.Close();
        }

        public List<string> getPccList(string texName)
        {
            List<string> pccList = new List<string>();
            SQLiteCommand command;
            SQLiteDataReader reader;

            string recoverPcc = "SELECT DISTINCT Name FROM 'tbl_Pcc', 'tbl_Tex', 'tbl_TexPcc' WHERE FullName LIKE '%" + texName + "' AND tbl_Tex.Id = tbl_TexPcc.TexId AND tbl_Pcc.Id = tbl_TexPcc.PccId ORDER BY Name";
            command = new SQLiteCommand(recoverPcc, sqlDBConn);
            reader = command.ExecuteReader();

            while (reader.Read())
                pccList.Add(reader[0].ToString());

            return pccList;
        }

        public int completeTexList(TreeView treeView, string pccFileName = null, string texName = null)
        {
            int countRows = 0;

            string recoverTexId = "SELECT FullName AS Tex, Name AS Pcc FROM tbl_Tex, tbl_Pcc, tbl_TexPcc WHERE tbl_Tex.Id = tbl_TexPcc.TexId AND tbl_Pcc.Id = tbl_TexPcc.PccId";
            if (pccFileName != null && pccFileName != "")
                recoverTexId += " AND tbl_Pcc.Name LIKE '%" + pccFileName + "'";
            if (texName != null)
                recoverTexId += " AND tbl_Tex.FullName LIKE '%" + texName + "%'";
            recoverTexId += " GROUP BY FullName ORDER BY FullName";

            SQLiteCommand command = new SQLiteCommand(recoverTexId, sqlDBConn);
            SQLiteDataReader reader = command.ExecuteReader();
            treeView.BeginUpdate();
            treeView.Nodes.Clear();
            treeView.Sort();
            while (reader.Read())
            {
                string fullTextureName = reader["Tex"].ToString();
                string[] pkgChunks = fullTextureName.Split('.');

                TreeNode texNode = null;

                foreach (string package in pkgChunks)
                {
                    texNode = (texNode == null) ?
                        treeView.Nodes.ContainsKey(package) ? treeView.Nodes[package] : treeView.Nodes.Add(package, package.ToUpperInvariant()) :
                        texNode.Nodes.ContainsKey(package) ? texNode.Nodes[package] : texNode.Nodes.Add(package, package);
                }

                texNode.Name = reader["Pcc"].ToString();

                countRows++;
            }
            treeView.EndUpdate();

            return countRows;
        }

        public void completePccList(TreeView treeView, string pccName = null)
        {
            string recoverPccId = "SELECT Name FROM tbl_Pcc";
            if (pccName != null)
                recoverPccId += " WHERE Name LIKE '%" + pccName + "%'";
            recoverPccId += " ORDER BY Name";
            SQLiteCommand command = new SQLiteCommand(recoverPccId, sqlDBConn);
            SQLiteDataReader reader = command.ExecuteReader();
            treeView.BeginUpdate();
            treeView.Nodes.Clear();

            TreeNode root = treeView.Nodes.Add("", "Default game");

            while (reader.Read())
            {
                string pccFileName = reader["Name"].ToString();

                root.Nodes.Add(ME3Paths.cookedPath + pccFileName, pccFileName);
            }
            treeView.EndUpdate();
        }
    }
}
